| python模块之XlsxWriter | 您所在的位置：网站首页 › xlsxwriter 官网 › python模块之XlsxWriter |
python模块之XlsxWriter
|
官网Tutorial：http://xlsxwriter.readthedocs.io/tutorial Xlsx是python用来构造xlsx文件的模块，可以向excel2007+中写text，numbers，formulas 公式以及hyperlinks超链接。 可以完成xlsx文件的自动化构造，包括： 合并单元格，制作excel图表等功能：
1，Introduction: xlsxWriter支持多种excle功能；与excel完美兼容；写大文件，速度快且只占用很小的内存空间 不支持读或者改现有的excel文件 2， Installing: sudo pip install XlsxWriter; sudo easy_install XlsxWriter; 或者源码安装：http://github.com/jmcnamara/XlsxWriter/archive/master.tar.gz 3，使用： import xlsxwriter workbook = xlsxwriter.Workbook('hello.xlsx') # 建立文件 worksheet = workbook.add_worksheet() # 建立sheet， 可以work.add_worksheet('employee')来指定sheet名，但中文名会报UnicodeDecodeErro的错误 worksheet.write('A1', 'Hello world') # 向A1写入 workbook.close()excel公式计算 # Some data we want to write to the worksheet. expenses = ( ['Rent', 1000], ['Gas', 100], ['Food', 300], ['Gym', 50], ) # Start from the first cell. Rows and columns are zero indexed. 按标号写入是从0开始的，按绝对位置'A1'写入是从1开始的 row = 0 col = 0 # Iterate over the data and write it out row by row. for item, cost in (expenses): worksheet.write(row, col, item) worksheet.write(row, col + 1, cost) row += 1 # Write a total using a formula. worksheet.write(row, 0, 'Total') worksheet.write(row, 1, '=SUM(B1:B4)') # 调用excel的公式表达式 workbook.close()excel自定义格式： import xlsxwriter # 建文件及sheet. workbook = xlsxwriter.Workbook('Expenses02.xlsx') worksheet = workbook.add_worksheet() # Add a bold format to use to highlight cells. 设置粗体，默认是False bold = workbook.add_format({'bold': True}) # Add a number format for cells with money. 定义数字格式 money = workbook.add_format({'num_format': '$#,##0'}) # Write some data headers. 带自定义粗体blod格式写表头 worksheet.write('A1', 'Item', bold) worksheet.write('B1', 'Cost', bold) # Some data we want to write to the worksheet. expenses = ( ['Rent', 1000], ['Gas', 100], ['Food', 300], ['Gym', 50], ) # Start from the first cell below the headers. row = 1 col = 0 # Iterate over the data and write it out row by row. for item, cost in (expenses): worksheet.write(row, col, item) # 带默认格式写入 worksheet.write(row, col + 1, cost, money) # 带自定义money格式写入 row += 1 # Write a total using a formula. worksheet.write(row, 0, 'Total', bold) worksheet.write(row, 1, '=SUM(B2:B5)', money) workbook.close()excel写入时间格式 from datetime import datetime import xlsxwriter # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook('Expenses03.xlsx') worksheet = workbook.add_worksheet() # Add a bold format to use to highlight cells. bold = workbook.add_format({'bold': 1}) # Add a number format for cells with money. money_format = workbook.add_format({'num_format': '$#,##0'}) # Add an Excel date format. date_format = workbook.add_format({'num_format': 'mmmm d yyyy'}) # Adjust the column width. worksheet.set_column(1, 1, 15) # Write some data headers. worksheet.write('A1', 'Item', bold) worksheet.write('B1', 'Date', bold) worksheet.write('C1', 'Cost', bold) # Some data we want to write to the worksheet. expenses = ( ['Rent', '2013-01-13', 1000], ['Gas', '2013-01-14', 100], ['Food', '2013-01-16', 300], ['Gym', '2013-01-20', 50], ) # Start from the first cell below the headers. row = 1 col = 0 for item, date_str, cost in (expenses): # Convert the date string into a datetime object. date = datetime.strptime(date_str, "%Y-%m-%d") worksheet.write_string (row, col, item ) worksheet.write_datetime(row, col + 1, date, date_format ) worksheet.write_number (row, col + 2, cost, money_format) row += 1 # Write a total using a formula. worksheet.write(row, 0, 'Total', bold) worksheet.write(row, 2, '=SUM(C2:C5)', money_format) workbook.close() @@@ write方法提供了python类型到excel类型的转换， XlsxWriter支持excel工作表最大1048576行记录，16384条列记录，超出可以选择再建新sheet worksheet.write(0, 0, 'Hello') # write_string() worksheet.write(1, 0, 'World') # write_string() worksheet.write(2, 0, 2) # write_number() worksheet.write(3, 0, 3.00001) # write_number() worksheet.write(4, 0, '=SIN(PI()/4)') # write_formula() worksheet.write(5, 0, '') # write_blank() worksheet.write(6, 0, None) # write_blank()关于更多字符串、数字、颜色及位置等excel格式：http://xlsxwriter.readthedocs.io/format.html 4， 图标 这个是我比较关注的利用excel工具进行图标统计的功能 相比较python的matplotlib的画图模块，excel的图标更加漂亮灵活一些 Chart: Area, Bar, Column, Doughnut, Line, Pie, Scatter, Stock, Radar workbook = xlswriter.Workbook('chart.xls') worksheet = workbook.add_sheet('First_example') # 普通工作表 建立Chart对象： chart = workbook.add_chart({type, 'column'}) 将图插入到sheet中： worksheet.insert_chart('A7', chart) 或者可以建立图表工作表chartsheet chartsheet = workbook.add_charsheet() chartsheet.set_char(chart) 柱状图： import xlsxwriter workbook = xlsxwriter.Workbook('chart.xlsx') worksheet = workbook.add_worksheet() # Create a new Chart object. chart = workbook.add_chart({'type': 'column'}) # Write some data to add to plot on the chart. data = [ [1, 2, 3, 4, 5], [2, 4, 6, 8, 10], [3, 6, 9, 12, 15], ] worksheet.write_column('A1', data[0]) # 按列插入 worksheet.write_column('B1', data[1]) worksheet.write_column('C1', data[2]) # Configure the chart. In simplest case we add one or more data series. chart.add_series({'values': '=Sheet1!$A$1:$A$5'}) chart.add_series({'values': '=Sheet1!$B$1:$B$5'}) chart.add_series({'values': '=Sheet1!$C$1:$C$5'}) # Insert the chart into the worksheet. worksheet.insert_chart('A7', chart) workbook.close()workbook.add_chart({'type':'column'}) # 默认格式 workbook.add_chart({'type':'column', 'substyle':'percent_stacked'}) # 按百分比展示
workbook.add_chart({'type':'column', 'substyle':'stacked'})
|
【本文地址】